# persistence.py
from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, relation
#from models.user import User
#from models.path import Path
from models.album import Album
from models.image import Image
from util import user_data_dir
import os
import logging
import string

# - create the directory if necessary
user_data_path = user_data_dir('Yapss', 'Yapss')
if not os.path.isdir(user_data_path):
    os.makedirs(user_data_path)

# - create the SA engine
# this format works: 'sqlite:////Documents and Settings/metadata.txt'
if os.name != 'posix':
    data_file_path = os.path.join(user_data_path, 'metadata.db')
    dbfile_path = string.replace(data_file_path[2:], '\\', '/')
else:
    dbfile_path = os.path.join(user_data_path, 'metadata.db')
    
logging.debug('persistence: database at: %s' % dbfile_path)
    
# use str to convert unicode to ascii
dsn = str('sqlite:///%s' % dbfile_path)
logging.info('application data dsn is: %s' % dsn)
engine = create_engine(dsn)
metadata = MetaData()
metadata.bind = engine

# - create the tables
#users_table = Table('user', metadata,
#    Column('id', Integer, primary_key=True),
#    Column('name', String(40)),
#    Column('password', String(15))
#)
#
#paths_table = Table('path', metadata,
#    Column('id', Integer, primary_key=True),
#    Column('path', String(40)))
#
#user_paths = Table('user_path', metadata,
#                   Column('user_id', Integer, ForeignKey('user.id')),
#                   Column('path_id', Integer, ForeignKey('path.id')))

albums_table = Table('album', metadata,
    Column('id', Integer, primary_key=True),
    Column('path', String(40)))

images_table = Table('image', metadata, 
    Column('id', Integer, primary_key=True),
    Column('album_id', Integer, ForeignKey('album.id')),
    Column('path', String(40)))

# - create the tables if tables are created yet
metadata.create_all(engine)

# - create the session and mapper
session = scoped_session(sessionmaker(autoflush=True, transactional=True))
mapper = session.mapper

# - ORM mapping
#mapper(User, users_table)
#mapper(Path, paths_table, properties = {'viewers':relation(User, secondary=user_paths, backref='paths')})
mapper(Image, images_table)
mapper(Album, albums_table, properties = {'images': relation(Image, backref='album', cascade='all, delete, delete-orphan')})

def dbtransaction(func):
    def deco(*args, **kws):
        try:
            r = func(*args, **kws)
        except:
            session.rollback()
            raise
        else:
            session.flush()
            session.commit()
        finally:
            session.close()
        return r
    return deco



